from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
from typing import Any, Dict, List
from fastapi import HTTPException

class PostgresDB:
    def __init__(self, database_url) -> None:
        self.database_url = database_url
        self.engine = create_engine(self.database_url)
        self.sessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=self.engine)

    def execute_sql(self, sql: str, params: Dict[str, Any] = None) -> List[Dict]:
    
        with self.sessionLocal() as session:
            try:
                # 使用 text() 创建 SQL 语句
                result = session.execute(text(sql), params or {})
                
                # 获取列名
                columns = result.keys()
                # 将结果转换为字典列表
                results = [dict(zip(columns, row)) for row in result]
                
                return results
            except Exception as e:
                raise HTTPException(status_code=500, detail=f"SQL执行错误: {str(e)}")



